{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime,timedelta\n",
    "SQLALCHEMY_DATABASE_URL = \"mysql+pymysql://root:yongs81@47.113.124.50:3306/demo\"\n",
    "engine = create_engine(SQLALCHEMY_DATABASE_URL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now = datetime.now()\n",
    "now = datetime(2020,4,27,20,0,0)\n",
    "#start = now - timedelta(minutes=60)\n",
    "start = now - timedelta(minutes=720)\n",
    "now = now.strftime('%Y-%m-%d %H:%M:%S')\n",
    "start = start.strftime('%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-04-27 20:00:00'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-04-27 08:00:00'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"select * from analyse_pause where StartTime between '{0}' AND '{1}' AND isActive = True;\".format(start,now)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>StartLine</th>\n",
       "      <th>StartVarName</th>\n",
       "      <th>Interval</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>w5</td>\n",
       "      <td>线尾包装段急停</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>w2</td>\n",
       "      <td>卤检急停</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>w7</td>\n",
       "      <td>商检房入口急停</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>w7</td>\n",
       "      <td>吹水段到静音房急停</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>w7</td>\n",
       "      <td>真空线插线急停</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2401</td>\n",
       "      <td>w10</td>\n",
       "      <td>商检出口到静音房段急</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2402</td>\n",
       "      <td>w5</td>\n",
       "      <td>卤检到安参段急停</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2403</td>\n",
       "      <td>w6</td>\n",
       "      <td>线头链板（线尾）</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2404</td>\n",
       "      <td>w7</td>\n",
       "      <td>上盖板段急停</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2405</td>\n",
       "      <td>w5</td>\n",
       "      <td>商检出口到转盘急</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2406 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     StartLine StartVarName  Interval\n",
       "0           w5      线尾包装段急停         1\n",
       "1           w2         卤检急停         4\n",
       "2           w7      商检房入口急停         5\n",
       "3           w7    吹水段到静音房急停        34\n",
       "4           w7      真空线插线急停         2\n",
       "...        ...          ...       ...\n",
       "2401       w10   商检出口到静音房段急        39\n",
       "2402        w5     卤检到安参段急停        10\n",
       "2403        w6     线头链板（线尾）        10\n",
       "2404        w7       上盖板段急停        34\n",
       "2405        w5     商检出口到转盘急       106\n",
       "\n",
       "[2406 rows x 3 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=df.loc[:,[\"StartLine\",\"StartVarName\",\"Interval\"]]\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df1.groupby(by=[\"StartLine\",\"StartVarName\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = df1.groupby(by=[\"StartVarName\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'卤检到安参段急停': Int64Index([ 53,  73,  74,  75,  91, 101, 106, 174, 175, 176, 177, 243, 250,\n",
       "             288, 290, 315, 320, 321, 327, 333, 354, 355, 356, 358, 368, 371,\n",
       "             372, 373, 374, 375, 376, 379, 384, 388, 394, 396, 397, 408],\n",
       "            dtype='int64'),\n",
       " '卤检段急停': Int64Index([  6,   9,  10,  11,  72,  96, 143, 148, 261, 266, 268, 279, 289,\n",
       "             293, 294, 297, 336, 337],\n",
       "            dtype='int64'),\n",
       " '商检出口到转盘急': Int64Index([  1,   2,   3,   7,   8,  12,  13,  14,  15,  17,\n",
       "             ...\n",
       "             387, 390, 392, 395, 398, 399, 401, 402, 407, 409],\n",
       "            dtype='int64', length=153),\n",
       " '商检房出口急停': Int64Index([58, 249, 251, 357], dtype='int64'),\n",
       " '安参段急停': Int64Index([171], dtype='int64'),\n",
       " '真空线入口急停': Int64Index([90, 215, 235], dtype='int64'),\n",
       " '真空线插线急停': Int64Index([21, 29, 32, 313, 330, 332, 335], dtype='int64'),\n",
       " '线头上接头段': Int64Index([18, 43, 345, 406], dtype='int64'),\n",
       " '线头段板（线中）': Int64Index([94], dtype='int64'),\n",
       " '线头链板（线中）': Int64Index([ 25,  88,  89,  93,  97,  98, 107, 109, 112, 113, 119, 120, 122,\n",
       "             124, 127, 130, 131, 133, 135, 137, 139, 142, 145, 147, 151, 153,\n",
       "             154, 157, 161, 163, 165, 168, 169, 173, 178, 182, 185, 190, 193,\n",
       "             194, 195, 198, 200, 202, 204, 205, 206, 207, 210, 242, 245, 252,\n",
       "             254, 256, 264, 265, 278, 282, 286, 287, 301, 303, 312, 323],\n",
       "            dtype='int64'),\n",
       " '线头链板（线尾）': Int64Index([ 99, 103, 105, 226, 259, 260, 275, 296, 318, 328, 331, 334, 338,\n",
       "             341, 342, 344, 353, 361, 365, 367, 370, 389, 403, 404],\n",
       "            dtype='int64'),\n",
       " '线尾包装段急停': Int64Index([  0,   4,   5,  16,  19,  23,  24,  36,  41,  44,  45,  60,  61,\n",
       "              63,  76,  85,  86,  87, 100, 102, 104, 116, 129, 132, 136, 138,\n",
       "             140, 141, 149, 150, 156, 160, 162, 164, 167, 179, 180, 196, 197,\n",
       "             199, 201, 203, 209, 211, 217, 218, 219, 220, 221, 222, 225, 229,\n",
       "             230, 233, 244, 258, 263, 269, 271, 272, 280, 281, 284, 291, 292,\n",
       "             310, 314, 322, 325, 326, 339, 340, 343, 346, 347, 350, 359, 360,\n",
       "             362, 364, 366, 369, 378, 380, 382, 385, 386, 391, 393, 400, 405],\n",
       "            dtype='int64'),\n",
       " '转盘到套箱急停': Int64Index([184, 227], dtype='int64')}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StartLine       w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5w5...\n",
       "StartVarName    线头链板（线中）线头链板（线中）线头链板（线中）线头链板（线中）线头链板（线中）线头链板（线...\n",
       "Interval                                                     2995\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.get_group(\"线头链板（线中）\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StartVarName\n",
       "卤检到安参段急停     38\n",
       "卤检段急停        18\n",
       "商检出口到转盘急    153\n",
       "商检房出口急停       4\n",
       "安参段急停         1\n",
       "真空线入口急停       3\n",
       "真空线插线急停       7\n",
       "线头上接头段        4\n",
       "线头段板（线中）      1\n",
       "线头链板（线中）     64\n",
       "线头链板（线尾）     24\n",
       "线尾包装段急停      91\n",
       "转盘到套箱急停       2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_groups(group_obj):\n",
    "    for name,group in group_obj:\n",
    "        print(name)\n",
    "        print(group.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "卤检到安参段急停\n",
      "   StartLine StartVarName  Interval\n",
      "53        w5     卤检到安参段急停       154\n",
      "73        w5     卤检到安参段急停         1\n",
      "74        w5     卤检到安参段急停         2\n",
      "75        w5     卤检到安参段急停         5\n",
      "91        w5     卤检到安参段急停         1\n",
      "卤检段急停\n",
      "   StartLine StartVarName  Interval\n",
      "6         w5        卤检段急停        21\n",
      "9         w5        卤检段急停         2\n",
      "10        w5        卤检段急停        17\n",
      "11        w5        卤检段急停        11\n",
      "72        w5        卤检段急停         6\n",
      "商检出口到转盘急\n",
      "  StartLine StartVarName  Interval\n",
      "1        w5     商检出口到转盘急         0\n",
      "2        w5     商检出口到转盘急        34\n",
      "3        w5     商检出口到转盘急        24\n",
      "7        w5     商检出口到转盘急        49\n",
      "8        w5     商检出口到转盘急        25\n",
      "商检房出口急停\n",
      "    StartLine StartVarName  Interval\n",
      "58         w5      商检房出口急停        19\n",
      "249        w5      商检房出口急停        31\n",
      "251        w5      商检房出口急停        39\n",
      "357        w5      商检房出口急停        11\n",
      "安参段急停\n",
      "    StartLine StartVarName  Interval\n",
      "171        w5        安参段急停         3\n",
      "真空线入口急停\n",
      "    StartLine StartVarName  Interval\n",
      "90         w5      真空线入口急停        18\n",
      "215        w5      真空线入口急停        23\n",
      "235        w5      真空线入口急停        21\n",
      "真空线插线急停\n",
      "    StartLine StartVarName  Interval\n",
      "21         w5      真空线插线急停        94\n",
      "29         w5      真空线插线急停       248\n",
      "32         w5      真空线插线急停        46\n",
      "313        w5      真空线插线急停        31\n",
      "330        w5      真空线插线急停        17\n",
      "线头上接头段\n",
      "    StartLine StartVarName  Interval\n",
      "18         w5       线头上接头段       225\n",
      "43         w5       线头上接头段        78\n",
      "345        w5       线头上接头段        39\n",
      "406        w5       线头上接头段        17\n",
      "线头段板（线中）\n",
      "   StartLine StartVarName  Interval\n",
      "94        w5     线头段板（线中）        37\n",
      "线头链板（线中）\n",
      "   StartLine StartVarName  Interval\n",
      "25        w5     线头链板（线中）         3\n",
      "88        w5     线头链板（线中）        18\n",
      "89        w5     线头链板（线中）        58\n",
      "93        w5     线头链板（线中）        18\n",
      "97        w5     线头链板（线中）        32\n",
      "线头链板（线尾）\n",
      "    StartLine StartVarName  Interval\n",
      "99         w5     线头链板（线尾）         8\n",
      "103        w5     线头链板（线尾）        77\n",
      "105        w5     线头链板（线尾）        62\n",
      "226        w5     线头链板（线尾）        34\n",
      "259        w5     线头链板（线尾）        52\n",
      "线尾包装段急停\n",
      "   StartLine StartVarName  Interval\n",
      "0         w5      线尾包装段急停         1\n",
      "4         w5      线尾包装段急停         1\n",
      "5         w5      线尾包装段急停         1\n",
      "16        w5      线尾包装段急停         0\n",
      "19        w5      线尾包装段急停        33\n",
      "转盘到套箱急停\n",
      "    StartLine StartVarName  Interval\n",
      "184        w5      转盘到套箱急停        17\n",
      "227        w5      转盘到套箱急停       194\n"
     ]
    }
   ],
   "source": [
    "print_groups(grouped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Interval</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StartVarName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>商检出口到转盘急</td>\n",
       "      <td>5057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>线头链板（线中）</td>\n",
       "      <td>2995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>线尾包装段急停</td>\n",
       "      <td>2347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>卤检到安参段急停</td>\n",
       "      <td>1522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>线头链板（线尾）</td>\n",
       "      <td>1155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>真空线插线急停</td>\n",
       "      <td>490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>线头上接头段</td>\n",
       "      <td>359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>卤检段急停</td>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>转盘到套箱急停</td>\n",
       "      <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>商检房出口急停</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>真空线入口急停</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>线头段板（线中）</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>安参段急停</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Interval\n",
       "StartVarName          \n",
       "商检出口到转盘急          5057\n",
       "线头链板（线中）          2995\n",
       "线尾包装段急停           2347\n",
       "卤检到安参段急停          1522\n",
       "线头链板（线尾）          1155\n",
       "真空线插线急停            490\n",
       "线头上接头段             359\n",
       "卤检段急停              291\n",
       "转盘到套箱急停            211\n",
       "商检房出口急停            100\n",
       "真空线入口急停             62\n",
       "线头段板（线中）            37\n",
       "安参段急停                3"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.sum().sort_values(by=\"Interval\",ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
